/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import dto.AccountDTO;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Account DAO
 * @author Hung Cuong <nhc.hcmuns at gmail.com>
 */
public class AccountDAO {
    MSAccessProvider _Provider;

    public AccountDAO() {
        _Provider = new MSAccessProvider();
    }

    /**
     * Insert account
     * @param dto
     */
    public void insert(AccountDTO acc) {

        String insertQuery = "INSERT INTO Account(Username, Password, InHost, " +
                                                "OutHost, ProxySet, ProxyHost, ProxyPort) VALUES('" +
                                                acc.getUsername() + "', '" +
                                                acc.getPassword() + "', '" +
                                                acc.getInHost() + "', '" +
                                                acc.getOutHost() + "', '" +
                                                acc.getProxySet() + "', '" +
                                                acc.getProxyHost() + "', '" +
                                                acc.getProxyPort() + "')" ;

        _Provider.excuteNonQuery(insertQuery);
    }

    /**
     * Update account
     * @param acc
     */
    public void update(AccountDTO acc) {

        String updateQuery = "UPDATE Account SET " +
                             "Username = '" + acc.getUsername() + "', " +
                             "Password = '" + acc.getPassword() + "', " +
                             "InHost = '" + acc.getInHost() + "', " +
                             "OutHost = '" + acc.getOutHost() + "', " +
                             "ProxySet = " + acc.getProxySet() + ", " +
                             "ProxyHost = '" + acc.getProxyHost() + "', " +
                             "ProxyPort = '" + acc.getProxyPort() + "' " +
                             "WHERE Id = " + acc.getId();

        _Provider.excuteNonQuery(updateQuery);
    }

    /**
     * Delete account
     * @param acc
     */
    public void delete(AccountDTO acc) {
        String deleteQuery = "DELETE FROM Account WHERE Id=" + acc.getId();

        _Provider.excuteNonQuery(deleteQuery);
    }

    /**
     * Get all accounts
     * @return
     */
    public ArrayList<AccountDTO> getAllRecords(){
        ArrayList<AccountDTO> list = new ArrayList<AccountDTO>();

        String selectAllQuery = "SELECT * FROM Account";

        ResultSet rs = _Provider.executeQuery(selectAllQuery);

        if(rs != null){
            try {
                while (rs.next()) {
                    AccountDTO acc = new AccountDTO();
                    acc.setId(rs.getInt(1));
                    acc.setUsername(rs.getString(2));
                    acc.setPassword(rs.getString(3));
                    acc.setInHost(rs.getString(4));
                    acc.setOutHost(rs.getString(5));
                    acc.setProxySet(rs.getBoolean(6));
                    acc.setProxyHost(rs.getString(7));
                    acc.setProxyPort(rs.getString(8));
                    list.add(acc);
                }
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return list;
    }

    /**
     * Get account information
     * @param id
     * @return
     */
    public AccountDTO getInfo(int accId) {
        AccountDTO acc = new AccountDTO();

        String contactQuery = "SELECT * FROM Account WHERE Id = " + accId;

        ResultSet rs = _Provider.executeQuery(contactQuery);

        if(rs != null){
            try {
                rs.next();
                acc.setId(rs.getInt(1));
                acc.setUsername(rs.getString(2));
                acc.setPassword(rs.getString(3));
                acc.setInHost(rs.getString(4));
                acc.setOutHost(rs.getString(5));
                acc.setProxySet(rs.getBoolean(6));
                acc.setProxyHost(rs.getString(7));
                acc.setProxyPort(rs.getString(8));
            } catch (SQLException ex) {
                Logger.getLogger(AccountDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }

        return acc;
    }
}
